package online.sh.cn.fy.common.api;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.HashedMap;

import online.sh.cn.fy.common.utils.JDBCUtil;



public class SqlUtil {
	private  Connection conn;  
    private  PreparedStatement  st; 
    private  ResultSet rs;
    private  Map<String,Object> map;
    private  List<Object> list;
    
    public static String getSystime(){
    	String time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
    	return time;
    } 
    
    public static String getSystimeSign(){
    	String time = new SimpleDateFormat("yyyy-MM-dd").format(Calendar.getInstance().getTime());
    	return time;
    } 
    
    
    //更新插入token进入数据库
    public void updateAccessToken(String accesstoken){
    	JDBCUtil ju=new JDBCUtil();
    	conn=ju.connect();
		try {
			String sql="update wxtoken set token=?,freshtime=?";
			st=(PreparedStatement) conn.prepareStatement(sql); 
			st.setString(1, accesstoken);
			st.setString(2, getSystime());
			st.executeUpdate();
			System.out.println("更新accesstoken成功！");
		} catch (SQLException e) {  
            System.out.println("更新accesstoken失败！" + e.getMessage());
		} finally {
			try {
				conn.close();
				st.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		}  
    }
    
    //取出accesstoken
    public String getAccesstoken(){
    	String result="";
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from wxtoken";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            while (rs.next()) {
            	result=rs.getString("token");
            }
            return result;
        } catch (SQLException e) {  
            System.out.println("获取token值失败："+e.getMessage());
            return null;
        } finally {
			try {
				conn.close();
				st.close();
				rs.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		}  
    }
    
  //更新插入ticket进入数据库
    public void updateJsTicket(String ticket){
    	JDBCUtil ju=new JDBCUtil();
    	conn=ju.connect();
		try {
			String sql="update wxjsticket set ticket=?,freshtime=?";
			st=(PreparedStatement) conn.prepareStatement(sql); 
			st.setString(1, ticket);
			st.setString(2, getSystime());
			st.executeUpdate();
			System.out.println("更新ticket成功！");
		} catch (SQLException e) {  
            System.out.println("更新ticket失败！" + e.getMessage());
		} finally {
			try {
				conn.close();
				st.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		} 
    }
    
    //取出jsapi ticket
    public String getJsTicket(){
    	String result="";
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from wxjsticket";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            while (rs.next()) {
            	result=rs.getString("ticket");
            }
            return result;
        } catch (SQLException e) {  
            System.out.println("获取ticket值失败："+e.getMessage());
            return null;
        } finally {
			try {
				conn.close();
				st.close();
				rs.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		} 
    }
    
  //取出jsapi ticketTime
    public String getJsTicketTime(){
    	String result="";
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from wxjsticket";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            while (rs.next()) {
            	result=rs.getString("freshtime");
            }
            return result;
        } catch (SQLException e) {  
            System.out.println("获取ticketfreshtime值失败："+e.getMessage());
            return null;
        } finally {
			try {
				conn.close();
				st.close();
				rs.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		} 
    }
    
    
    //插入用户签到数据
    //openid     用户id
    //lasttime   最后签到时间
    //total  	  累计签到
    //state		  连续签到
    //today		  今日签到状态
    //month      签到月份
    //year		  签到年份
    //inrow      签到日期集合
    //norow      未签到集合
    public void insertSignUser(String openid,int total,int state,int today,int todaypoints,int statepoints,int six,int month,int year,String inrow,String norow){
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "insert into fysignuser(userid,lastest_time,sign_total,sign_state,sign_today,today_points,state_points,sign_six,sign_month,sign_year,sign_setday,sign_notday) "
            		+ "values(?,?,?,?,?,?,?,?,?,?,?,?)";  
            st = (PreparedStatement) conn.prepareStatement(sql);
            st.setString(1, openid);
			st.setString(2, getSystimeSign());
			st.setInt(3, total);
			st.setInt(4, state);
			st.setInt(5, today);
			st.setInt(6, todaypoints);
			st.setInt(7, statepoints);
			st.setInt(8, six);
			st.setInt(9, month);
			st.setInt(10, year);
			st.setString(11, inrow);
			st.setString(12, norow);
            int count = st.executeUpdate(); 
            System.out.println("写入young粉参与签到信息" + count + " 条数据"); 
        } catch (SQLException e) {  
            System.out.println("写入young粉参与签到信息失败" + e.getMessage());  
        } finally {
			try {
				conn.close();
				st.close();
				rs.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		}   
    }
    
    
    //插入分享用户好友补签数据
    //openid     用户id
    //helpid     用户好友id
    //nickname   好友昵称
    //image      好友头像
    //date       补签
    public void insertHelpSignUser(String openid,String helpid,String nickname,String image,int year,int month){
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "insert into fysignuser_help(userid,user_helpid,sign_nickname,sign_img,sign_date,sign_year,sign_month) "
            		+ "values(?,?,?,?,?,?,?)";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            st.setString(1, openid);
			st.setString(2, helpid);
			st.setString(3, nickname);
			st.setString(4, image);
			st.setString(5, getSystimeSign());
			st.setInt(6, year);
			st.setInt(7, month);
            int count = st.executeUpdate(); 
            System.out.println("写入youngflow月参与签到领流量认证用户好友补签信息" + count + " 条数据"); 
        } catch (SQLException e) {  
            System.out.println("写入youngflow月签到领流量认证用户好友补签信息失败" + e.getMessage());  
        } finally {
			try {
				conn.close();
				st.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		}   
    }
    
    
    //查询用户签到状态
    public Map checkUserSignUp(String openid,int year,int month){
    	map=new HashedMap();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select userid,lastest_time,sign_total,sign_state,sign_year,sign_today,today_points,state_points,sign_six,sign_month,sign_setday,sign_notday from fysignuser where userid=? and sign_year=? and sign_month=?";  
            st = (PreparedStatement) conn.prepareStatement(sql); 
            st.setString(1, openid);
			st.setInt(2, year);
			st.setInt(3, month);
            rs = st.executeQuery();
            if (rs.next()) {
            	map.put("userid", rs.getString("userid").trim());
            	map.put("lastest_time", rs.getString("lastest_time").trim());
            	map.put("sign_total", rs.getInt("sign_total"));
            	map.put("sign_state", rs.getInt("sign_state"));
            	map.put("sign_month", rs.getInt("sign_year"));
            	map.put("sign_today", rs.getInt("sign_today"));
            	map.put("today_points", rs.getInt("today_points"));
            	map.put("state_points", rs.getInt("state_points"));
            	map.put("sign_six", rs.getInt("sign_six"));
            	map.put("sign_month", rs.getInt("sign_month"));
            	map.put("sign_setday", rs.getString("sign_setday").trim());
            	map.put("sign_notday", rs.getString("sign_notday").trim());
            }
            return map;
        } catch (SQLException e) {  
            System.out.println("查询youngflow月用户签到信息失败："+e.getMessage());
            return null;
        } finally {
			try {
				conn.close();
				st.close();
				rs.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		} 
    }
    
    //查询用户好友是否补签 限制一次
    public boolean checkUserSignHelpTime(String openid,String helpid,int year,int month){
    	boolean flag=false;
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "SELECT a.* FROM (SELECT * FROM fysignuser_help WHERE userid=?) a WHERE a.user_helpid=? and a.sign_year=? and a.sign_month=?";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            st.setString(1, openid);
            st.setString(2, helpid);
			st.setInt(3, year);
			st.setInt(4, month);
            rs = st.executeQuery();
            while (rs.next()) {
            	flag=true;
            }
            return flag;
        } catch (SQLException e) {  
            System.out.println("查询youngflow月好友补签次数限制列表失败："+e.getMessage());
            return flag;
        } finally {
			try {
				conn.close();
				st.close();
				rs.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		} 
    }
    
  //查询用户好友补签列表
    public List checkUserSignHelp(String openid,int year,int month){
    	list=new ArrayList();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select user_helpid,sign_nickname,sign_img,sign_date,sign_year,sign_month from fysignuser_help where userid=? and sign_year=? and sign_month=?";  
            st = (PreparedStatement) conn.prepareStatement(sql); 
            st.setString(1, openid);
			st.setInt(2, year);
			st.setInt(3, month);
            rs = st.executeQuery();
            while (rs.next()) {
            	map=new HashedMap();
            	map.put("user_helpid", rs.getString("user_helpid").trim());
            	map.put("sign_nickname", rs.getString("sign_nickname").trim());
            	map.put("sign_img", rs.getString("sign_img").trim());
            	map.put("sign_date", rs.getString("sign_date").trim());
            	map.put("sign_year", rs.getInt("sign_year"));
            	map.put("sign_month", rs.getInt("sign_month"));
            	list.add(map);
            }
            return list;
        } catch (SQLException e) {  
            System.out.println("查询youngflow月好友补签列表失败："+e.getMessage());
            return null;
        } finally {
			try {
				conn.close();
				st.close();
				rs.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		} 
    }
    
    //检查用户是否入库（判断首次签到）
    public boolean checkUser(String openid,int year,int month){
    	boolean flag=false;
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select userid from fysignuser where userid=? and sign_year=? and sign_month=?";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            st.setString(1, openid);
			st.setInt(2, year);
			st.setInt(3, month);
            rs = st.executeQuery();
            while (rs.next()) {
            	flag=true;
            }
            return flag;
        } catch (SQLException e) {  
            System.out.println("获取youngflow月签到用户user值失败："+e.getMessage());
            return flag;
        } finally {
			try {
				conn.close();
				st.close();
				rs.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		} 
    }
    
    //更新用户签到状态
    public void updateUserSignUp(String openid,String inrow,int year,int month){
    	JDBCUtil ju=new JDBCUtil();
    	conn=ju.connect();
		try {
			String sql="update fysignuser set lastest_time=?,sign_today=1,sign_total=sign_total+1,sign_setday=? where userid=? and sign_year=? and sign_month=?";
			st=(PreparedStatement) conn.prepareStatement(sql); 
			st.setString(1, getSystimeSign());
			st.setString(2, inrow);
			st.setString(3, openid);
			st.setInt(4, year);
			st.setInt(5, month);
			st.executeUpdate();
			System.out.println("更新youngflow月连续用户签到状态及累计成功！");
		} catch (SQLException e) {  
            System.out.println("更新youngflow月连续用户签到状态及累计失败！" + e.getMessage());
		} finally {
			try {
				conn.close();
				st.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		} 
    }
    
  //更新用户签到最大连续状态
    public void updateUserSignUpState(String openid,int state,int points,int statepoints,int year,int month,int six){
    	JDBCUtil ju=new JDBCUtil();
    	conn=ju.connect();
		try {
			String sql="update fysignuser set today_points=?,sign_state=?,state_points=state_points+?,sign_six=? where userid=? and sign_year=? and sign_month=?";
			st=(PreparedStatement) conn.prepareStatement(sql); 
			st.setInt(1, points);
			st.setInt(2, state);
			st.setInt(3, statepoints);
			st.setInt(4, six);
			st.setString(5, openid);
			st.setInt(6, year);
			st.setInt(7, month);
			st.executeUpdate();
			System.out.println("更新累计签到成功！");
		} catch (SQLException e) {  
            System.out.println("更新累计签到失败！" + e.getMessage());
		} finally {
			try {
				conn.close();
				st.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		} 
    }
    
  //更新用户补签状态
    public void updateUserSignHelpedSign(String openid,String signday,String unsignday,int year,int month){
    	JDBCUtil ju=new JDBCUtil();
    	conn=ju.connect();
		try {
			String sql="update fysignuser set sign_total=sign_total+1,sign_setday=?,sign_notday=? where userid=? and sign_year=? and sign_month=?";
			st=(PreparedStatement) conn.prepareStatement(sql); 
			st.setString(1, signday);
			st.setString(2, unsignday);
			st.setString(3, openid);
			st.setInt(4, year);
			st.setInt(5, month);
			st.executeUpdate();
			System.out.println("更新youngflow月补签用户成功！");
		} catch (SQLException e) {  
            System.out.println("更新youngflow月补签用户失败！" + e.getMessage());
		} finally {
			try {
				conn.close();
				st.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		} 
    }
    
  //更新用户签积分、经验值和级别
    public void updateUserExperience(String openid,int experience,int points,String level){
    	JDBCUtil ju=new JDBCUtil();
    	conn=ju.connect();
		try {
			String sql="update fyuser set experience=experience+?,points=points+?,level=? where openid=?";
			st=(PreparedStatement) conn.prepareStatement(sql); 
			st.setInt(1, experience);
			st.setInt(2, points);
			st.setString(3, level);
			st.setString(4, openid);
			st.executeUpdate();
			System.out.println("更新young粉积分、经验值、级别成功！");
		} catch (SQLException e) {  
            System.out.println("更新young粉积分、经验值、级别失败！" + e.getMessage());
		} finally {
			try {
				conn.close();
				st.close();
			} catch (SQLException e) {
				System.out.println("连接池关闭失败！");
			}
		} 
    }
    
}
